SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC [dbo].[PPlanStudentSemesterbt]
@nCode numeric(18,0),
@n_sem int,
@Ik_grup int
AS
SELECT
CU.ik_upContent,
cName_disc[Дисциплина],
cName_vid_zanyat[Вид работы],
NReg [Регистрационный №],
DateReg [Дата],
vz.iK_vid_zanyat[id занятия],
sd.iK_disc[id дисциплины]
FROM dbo.Student St INNER JOIN dbo.Zach Z 
ON St.nCode=Z.nCode 
INNER JOIN dbo.StudGrup SG 
ON Z.Ik_zach=SG.Ik_zach 
INNER JOIN dbo.Grup G 
ON SG.Ik_grup=G.Ik_grup 
INNER JOIN dbo.Uch_pl Up 
ON G.Ik_uch_plan=Up.ik_uch_plan 
INNER JOIN dbo.sv_disc sd 
ON Up.ik_uch_plan=sd.ik_uch_plan 
INNER JOIN dbo.discpln D 
ON sd.ik_disc=D.iK_disc 
INNER JOIN dbo.Content_UchPl CU 
ON sd.ik_disc_uch_plan=CU.ik_disc_uch_plan 
INNER JOIN dbo.Semester sem 
ON CU.n_sem=sem.n_sem 
INNER JOIN dbo.vid_zaniat vz 
ON CU.ik_vid_zanyat=vz.iK_vid_zanyat
full join
(select cu.ik_upContent, NReg, DateReg, Z.nCode from dbo.Content_UchPl cu
inner JOIN dbo.UspevDocument UD
ON CU.ik_upContent=UD.ik_upContent
inner JOIN dbo.UspevKPTheme UKPT
ON UKPT.idUspevDocs=UD.idUspevDocs 
inner JOIN dbo.Zach Z 
on UD.ik_zach=Z.Ik_zach) b
ON Z.nCode=b.nCode and CU.ik_upContent=b.ik_upContent
WHERE St.nCode=@nCode AND sem.n_sem=@n_sem AND @Ik_grup=SG.Ik_grup
AND SG.Ik_prikazZach IS NOT NULL 
AND SG.ik_pricOtch IS NULL ;
GO
